/*	DB Online printing service
	author:duyhoa
	copy right@2013 allright received
*/
USE master
GO
IF DB_ID('projectsem3') is not null
BEGIN
ALTER DATABASE projectsem3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE projectsem3
END
GO
CREATE DATABASE projectsem3
GO
USE projectsem3
GO
CREATE TABLE tblAdmin
(
	_aid int identity(1,1) primary key,
	_aname nvarchar(256),
	_apassword nvarchar(256),
	_arole smallint,
)
GO
CREATE TABLE tblUser
(
	_uid int identity(1,1) primary key,
	_uname nvarchar(256),
	_uaccount nvarchar(256) not null,
	_upassword nvarchar(256) not null,
	_ustatus smallint,
	_ucreatedate datetime,
)
GO
CREATE TABLE tblNews
(
	_nid int identity(1,1) primary key,
	_ntitle nvarchar(256),
	_ncontent nvarchar(4000),
	_ncreatedate datetime,
)
GO
CREATE TABLE tblImgSize
(
	_sid int identity(1,1) primary key,
	_swidth int,
	_sheight int,
	_sprice float,
)
GO
CREATE TABLE tblDiscount
(
	_sid int references tblImgSize(_sid),
	_did int identity(1,1) primary key,
	_drate float,
)
GO
CREATE TABLE tblImgAlbum
(
	_uid int references tblUser(_uid),
	_iaid int identity(1,1) primary key,
	_ianame nvarchar(256),
)
GO
CREATE TABLE tblImages
(
	_iaid int references tblImgAlbum(_iaid),
	_imgid int identity(1,1) primary key,
	_imgname nvarchar(1024),
	_imglink nvarchar(1024),
	_imgcreatedate datetime,
)
GO
CREATE TABLE tblOrder
(	
	_uid int references tblUser(_uid),
	_sid int references tblImgSize(_sid),
	_oid int identity(1,1) primary key,
	_ocreatedate datetime,
	_ostatus smallint,
)
GO

CREATE TABLE tblOrderDetail
(
	_imgid int references tblImages(_imgid),
	_oid int references tblOrder(_oid),
	_odid int identity(1,1) primary key,
	_price float,
	_quantity int,
	_status smallint,
	_timeship Datetime,
	_address varchar(MAX)
)
GO
--<--END CORE DB--<--
--<--INSERT TEMPLATES DATA--<--
INSERT INTO tblAdmin VALUES('admin','admin',0)
INSERT INTO tblAdmin VALUES('admin1','admin1',1)
INSERT INTO tblAdmin VALUES('admin2','admin2',2)
INSERT INTO tblAdmin VALUES('admin3','admin3',3)

INSERT INTO tblUser VALUES('Robert.Martin','user01','123456',1,GETDATE())
INSERT INTO tblUser VALUES('Maria.Ozawa','user02','123456',1,GETDATE())
INSERT INTO tblUser VALUES('Justin.Bieber','user03','123456',1,GETDATE())
INSERT INTO tblUser VALUES('Honore-De.Balzac','user04','123456',1,GETDATE())
INSERT INTO tblUser VALUES('Nokia-Lumina.920','user05','123456',1,GETDATE())
INSERT INTO tblUser VALUES('Jon-Van.Caneghem','user06','123456',1,GETDATE())

INSERT INTO tblNews VALUES('SALE OFF 25% FOR THIS MONTH','Today our manager decided decrease price for all services by 25%,The bonus will end date at:2/1/2000,fast to go sent order to us now!',1/1/2000)
INSERT INTO tblNews VALUES('BONUS FOR ORDER USING ATM','Today our manager decided decrease price for all services by 25%,The bonus will end date at:2/1/2000,fast to go sent order to us now!',1/1/2000)
INSERT INTO tblNews VALUES('1024 COLORS PER PIXEL','Today our manager decided decrease price for all services by 25%,The bonus will end date at:2/1/2000,fast to go sent order to us now!',1/1/2000)
INSERT INTO tblNews VALUES('ALL LONG/OLD USERS RECEIVED BONUS','Today our manager decided decrease price for all services by 25%,The bonus will end date at:2/1/2000,fast to go sent order to us now!',1/1/2000)
INSERT INTO tblNews VALUES('INCLUDED 1476/796 SIZE ON OUR SERVICE','Today our manager decided decrease price for all services by 25%,The bonus will end date at:2/1/2000,fast to go sent order to us now!',1/1/2000)
INSERT INTO tblNews VALUES('YOUR CHOICE HAVE BEEN MUCH MORE','Today our manager decided decrease price for all services by 25%,The bonus will end date at:2/1/2000,fast to go sent order to us now!',1/1/2000)
INSERT INTO tblNews VALUES('KIND YOUR MIND','Today our manager decided decrease price for all services by 25%,The bonus will end date at:2/1/2000,fast to go sent order to us now!',1/1/2000)
INSERT INTO tblNews VALUES('OUR COMPANY REGULARS','Today our manager decided decrease price for all services by 25%,The bonus will end date at:2/1/2000,fast to go sent order to us now!',1/1/2000)

INSERT INTO tblImgSize VALUES(640,480,200)
INSERT INTO tblImgSize VALUES(800,600,300)
INSERT INTO tblImgSize VALUES(1024,768,400)
INSERT INTO tblImgSize VALUES(1400,1050,500)
INSERT INTO tblImgSize VALUES(1600,1200,600)
INSERT INTO tblImgSize VALUES(1280,1024,700)
INSERT INTO tblImgSize VALUES(1920,1200,800)
INSERT INTO tblImgSize VALUES(1680,1050,900)
INSERT INTO tblImgSize VALUES(1280,800,1000)

INSERT INTO tblDiscount VALUES(1,5)
INSERT INTO tblDiscount VALUES(1,10)
INSERT INTO tblDiscount VALUES(1,15)
INSERT INTO tblDiscount VALUES(1,20)
INSERT INTO tblDiscount VALUES(1,25)
--<--Next:Procedure,Trigger,View,Function...--<--

